import os
import sys
import json
import datetime
import decimal
from typing import List

# 确保将项目根目录加入 sys.path
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if BASE_DIR not in sys.path:
    sys.path.insert(0, BASE_DIR)

# 初始化 Django 环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'cloud_platform.settings')
import django
from django.apps import apps
from django.db import models
from django.conf import settings

django.setup()

OUTPUT_PATH = os.path.join(os.path.dirname(__file__), 'mysql_dump.sql')

# 字段类型映射到 MySQL
def mysql_type_for_field(field: models.Field) -> str:
    # 外键：根据目标主键类型确定本字段类型
    if isinstance(field, models.ForeignKey):
        target_pk = field.target_field
        return mysql_type_for_field(target_pk)

    internal = field.get_internal_type()
    if internal in ('AutoField',):
        return 'INT'
    if internal in ('BigAutoField',):
        return 'BIGINT'
    if internal in ('IntegerField',):
        return 'INT'
    if internal in ('BigIntegerField',):
        return 'BIGINT'
    if internal in ('PositiveIntegerField',):
        return 'INT UNSIGNED'
    if internal in ('SmallIntegerField',):
        return 'SMALLINT'
    if internal in ('PositiveSmallIntegerField',):
        return 'SMALLINT UNSIGNED'
    if internal in ('CharField', 'EmailField', 'SlugField', 'URLField'):
        max_len = getattr(field, 'max_length', None) or 255
        return f'VARCHAR({max_len})'
    if internal in ('TextField',):
        return 'TEXT'
    if internal in ('JSONField',):
        return 'JSON'
    if internal in ('DateTimeField',):
        return 'DATETIME'
    if internal in ('DateField',):
        return 'DATE'
    if internal in ('TimeField',):
        return 'TIME'
    if internal in ('BooleanField',):
        return 'TINYINT(1)'
    if internal in ('BinaryField',):
        return 'LONGBLOB'
    if internal in ('FloatField',):
        return 'DOUBLE'
    if internal in ('DecimalField',):
        max_digits = getattr(field, 'max_digits', 10)
        decimal_places = getattr(field, 'decimal_places', 2)
        return f'DECIMAL({max_digits},{decimal_places})'
    if internal in ('UUIDField',):
        # 以文本保存 UUID
        return 'CHAR(36)'
    if internal in ('FileField', 'ImageField'):
        # 存储路径字符串
        max_len = getattr(field, 'max_length', None) or 255
        return f'VARCHAR({max_len})'
    # 兜底：文本
    return 'TEXT'


def default_sql_for_field(field: models.Field):
    # 使用原始 default 属性，避免调用默认函数被求值
    default = getattr(field, 'default', models.NOT_PROVIDED)
    if default is models.NOT_PROVIDED:
        return None
    if callable(default):
        return None
    return sql_value(default)


def sql_value(value):
    if value is None:
        return 'NULL'
    if isinstance(value, bool):
        return '1' if value else '0'
    if isinstance(value, (int, float)):
        if isinstance(value, float) and (value != value):  # NaN
            return 'NULL'
        return str(value)
    if isinstance(value, decimal.Decimal):
        return str(value)
    if isinstance(value, (datetime.datetime,)):
        # 转换为无时区字符串
        return f"'{value.strftime('%Y-%m-%d %H:%M:%S')}'"
    if isinstance(value, (datetime.date,)):
        return f"'{value.strftime('%Y-%m-%d')}'"
    if isinstance(value, (datetime.time,)):
        return f"'{value.strftime('%H:%M:%S')}'"
    # 其他作为字符串处理
    s = str(value)
    s = s.replace('\\', '\\\\').replace("'", "''")
    return f"'{s}'"


def fk_constraint_sql(field: models.ForeignKey) -> str:
    col = field.column
    target_table = field.remote_field.model._meta.db_table
    target_col = field.target_field.column
    # on_delete 映射
    od = field.remote_field.on_delete
    on_delete = 'RESTRICT'
    if od is models.CASCADE:
        on_delete = 'CASCADE'
    elif od is models.SET_NULL:
        on_delete = 'SET NULL'
    elif od is models.PROTECT:
        on_delete = 'RESTRICT'
    elif od is models.SET_DEFAULT:
        on_delete = 'SET DEFAULT'
    # 约束名
    cname = f"fk_{field.model._meta.db_table}_{col}"
    return f"CONSTRAINT `{cname}` FOREIGN KEY (`{col}`) REFERENCES `{target_table}`(`{target_col}`) ON DELETE {on_delete}"


def create_table_sql(model) -> str:
    table = model._meta.db_table
    lines: List[str] = []
    fk_lines: List[str] = []
    index_lines: List[str] = []

    # 注释：表说明
    table_comment = f"-- Table `{table}` from {model._meta.app_label}.{model.__name__}"
    comment_lines: List[str] = [table_comment]

    for field in model._meta.local_fields:
        col = field.column
        col_type = mysql_type_for_field(field)
        null_str = 'NULL' if field.null else 'NOT NULL'
        default_sql = default_sql_for_field(field)
        default_str = f" DEFAULT {default_sql}" if default_sql is not None else ''
        extra = ''
        if field.primary_key:
            # 自增主键
            if isinstance(field, (models.AutoField, models.BigAutoField)):
                extra = ' AUTO_INCREMENT'
        line = f"`{col}` {col_type} {null_str}{default_str}{extra}".strip()
        if getattr(field, 'unique', False):
            line += ' UNIQUE'
            # 注释：列唯一约束
            comment_lines.append(f"-- UNIQUE on `{col}`")
        lines.append(line)

        # 字段索引（db_index=True）
        if getattr(field, 'db_index', False) and not getattr(field, 'unique', False):
            iname = f"idx_{col}"
            index_lines.append(f"KEY `{iname}` (`{col}`)")
            comment_lines.append(f"-- INDEX `{iname}` on (`{col}`)")

        # 外键约束
        if isinstance(field, models.ForeignKey) and getattr(field, 'db_constraint', True):
            fk_sql = fk_constraint_sql(field)
            fk_lines.append(fk_sql)
            # 注释：外键关系
            target_table = field.remote_field.model._meta.db_table
            target_col = field.target_field.column
            od = field.remote_field.on_delete
            od_map = {models.CASCADE: 'CASCADE', models.SET_NULL: 'SET NULL', models.PROTECT: 'RESTRICT', models.SET_DEFAULT: 'SET DEFAULT'}
            on_delete = od_map.get(od, 'RESTRICT')
            cname = f"fk_{field.model._meta.db_table}_{col}"
            comment_lines.append(f"-- FK `{cname}`: `{col}` -> `{target_table}`(`{target_col}`) ON DELETE {on_delete}")

    # unique_together
    ut = getattr(model._meta, 'unique_together', []) or []
    for utup in ut:
        cols_conv = []
        for fname in utup:
            f = model._meta.get_field(fname)
            cols_conv.append(f'`{f.column}`')
        keyname = 'uniq_' + '_'.join(utup)
        lines.append(f"CONSTRAINT `{keyname}` UNIQUE ({', '.join(cols_conv)})")
        comment_lines.append(f"-- UNIQUE `{keyname}` on ({', '.join(cols_conv)})")

    # index_together
    it = getattr(model._meta, 'index_together', []) or []
    for itup in it:
        cols_conv = []
        for fname in itup:
            f = model._meta.get_field(fname)
            cols_conv.append(f'`{f.column}`')
        iname = 'idx_' + '_'.join(itup)
        index_lines.append(f"KEY `{iname}` ({', '.join(cols_conv)})")
        comment_lines.append(f"-- INDEX `{iname}` on ({', '.join(cols_conv)})")

    # Meta.indexes（仅处理字段索引）
    for idx in getattr(model._meta, 'indexes', []):
        fields = getattr(idx, 'fields', []) or []
        if fields:
            cols_conv = []
            for fname in fields:
                f = model._meta.get_field(fname)
                cols_conv.append(f'`{f.column}`')
            iname = getattr(idx, 'name', None) or ('idx_' + '_'.join(fields))
            index_lines.append(f"KEY `{iname}` ({', '.join(cols_conv)})")
            comment_lines.append(f"-- INDEX `{iname}` on ({', '.join(cols_conv)})")

    # 外键约束作为额外行
    lines.extend(fk_lines)
    # 索引行追加
    lines.extend(index_lines)

    inner = ',\n  '.join(lines)
    sql = (
        "" + '\n'.join(comment_lines) + "\n" +
        f"DROP TABLE IF EXISTS `{table}`;\n" +
        f"CREATE TABLE `{table}` (\n  {inner}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n"
    )
    return sql


def create_m2m_sql(field: models.ManyToManyField) -> str:
    through = field.remote_field.through
    if not through._meta.auto_created:
        # 自定义中间表在模型中会作为普通模型创建
        return ''
    return create_table_sql(through)


def insert_rows_sql(model) -> str:
    table = model._meta.db_table
    fields = [f for f in model._meta.local_fields]
    cols = ', '.join(f'`{f.column}`' for f in fields)
    lines: List[str] = []
    for obj in model.objects.all().iterator():
        values = []
        for f in fields:
            val = getattr(obj, f.attname)
            # 文件字段保存路径
            if isinstance(f, (models.FileField, models.ImageField)) and val:
                val = str(val)
            values.append(sql_value(val))
        lines.append(f"INSERT INTO `{table}` ({cols}) VALUES ({', '.join(values)});")
    if lines:
        header = f"-- Initial data for table `{table}` ({model._meta.app_label}.{model.__name__})\n"
        return header + '\n'.join(lines) + '\n'
    return ''


def main():
    # 生成 DDL 和 DML
    models_list = list(apps.get_models())
    # 排序：先内置 app，后用户 app，这里简单按表名排序
    models_list.sort(key=lambda m: m._meta.db_table)

    ddl_parts: List[str] = []
    dml_parts: List[str] = []
    m2m_parts: List[str] = []

    for m in models_list:
        ddl_parts.append(create_table_sql(m))
        # 自动生成的多对多中间表
        for mm in m._meta.local_many_to_many:
            m2m_sql = create_m2m_sql(mm)
            if m2m_sql:
                m2m_parts.append(m2m_sql)

    for m in models_list:
        dml = insert_rows_sql(m)
        if dml:
            dml_parts.append(dml)

    header = (
        '-- MySQL export generated from Django ORM\n'
        f"-- Project: cloud_platform | Generated at {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n"
        'SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\n\n'
    )
    footer = '\nSET FOREIGN_KEY_CHECKS=1;\n\n-- End of dump\n'

    content = header + '\n'.join(ddl_parts + m2m_parts) + '\n' + '\n'.join(dml_parts) + footer

    with open(OUTPUT_PATH, 'w', encoding='utf-8') as f:
        f.write(content)
    print(f'Exported MySQL SQL to: {OUTPUT_PATH}')


if __name__ == '__main__':
    main()


